'''
Created on 22/04/2009

@author: eh01
'''
from yarra.datastore.sqlite3dao import DSOSqlite3DAO
from yarra.datastore import sqlite3dao
import os


def create_database():
    conn = sqlite3dao.get_connection()
    cursor = conn.cursor()
        
    # Create DSO table 
    cursor.execute("""
                    CREATE TABLE DSOI (
                      ID INTEGER NOT NULL PRIMARY KEY,
                      URI TEXT,
                      TYPE TEXT,
                      ACTIVE INTEGER
                    )""")
    

    # Create delete trigger for DSO table
    cursor.execute("""
                    CREATE TRIGGER FK_DSOI_ID_DELETE_TRIGGER
                    BEFORE DELETE ON DSOI
                    FOR EACH ROW BEGIN
                        DELETE from DSOV WHERE DSOI_ID = OLD.ID;
                    END;
                    """) 
        
    # Create DSOV table
    cursor.execute("""
                    CREATE TABLE DSOV (
                      ID INTEGER NOT NULL PRIMARY KEY,
                      DSOI_ID INTEGER NOT NULL CONSTRAINT FK_DSOI_ID REFERENCES DSOI(ID) ON DELETE CASCADE,
                      VERSION INTEGER,
                      CREATED_TIMESTAMP TIMESTAMP
                    )""")


    # Create insert trigger for DSOV table
    cursor.execute("""
                    CREATE TRIGGER FK_DSOI_ID_INSERT_TRIGGER
                    BEFORE INSERT ON DSOV
                    FOR EACH ROW BEGIN
                        SELECT RAISE(ROLLBACK, 'INSERT ON TABLE "DSOV" VIOLATES FOREIGN KEY CONSTRAINT "FK_DSOI_ID"')
                        WHERE  NEW.DSOI_ID IS NOT NULL
                                AND (SELECT ID FROM DSOI WHERE id = NEW.DSOI_ID) IS NULL;
                    END;
                    """)
    
    # Create update trigger for DSOV table
    cursor.execute("""
                    CREATE TRIGGER FK_DSOI_ID_UPDATE_TRIGGER
                    BEFORE UPDATE ON DSOV
                    FOR EACH ROW BEGIN
                        SELECT RAISE(ROLLBACK, 'UPDATE ON TABLE "DSOV" VIOLATES FOREIGN KEY CONSTRAINT "FK_DSOI_ID"')
                        WHERE  NEW.DSOI_ID IS NOT NULL
                                AND (SELECT ID FROM DSOI WHERE id = NEW.DSOI_ID) IS NULL;
                    END;
                    """)       
                     
    # Create DSOV_DST table
    cursor.execute("""
                    CREATE TABLE DSOV_DST (
                      DSOV_ID INTEGER NOT NULL CONSTRAINT FK_DSOV_ID REFERENCES DSOV(ID),
                      DST_ID INTEGER NOT NULL CONSTRAINT FK_DST_ID REFERENCES DST(ID) ON DELETE CASCADE
                    )""")



    # Create insert trigger for DSOV table
    cursor.execute("""
                    CREATE TRIGGER FK_DSOV_ID_INSERT_TRIGGER
                    BEFORE INSERT ON DSOV_DST
                    FOR EACH ROW BEGIN
                        SELECT RAISE(ROLLBACK, 'INSERT ON TABLE "DSOV_DST" VIOLATES FOREIGN KEY CONSTRAINT "FK_DSOV_ID"')
                        WHERE  NEW.DSOV_ID IS NOT NULL
                                AND (SELECT ID FROM DSOV WHERE id = NEW.DSOV_ID) IS NULL;
                    END;
                    """)
    
    # Create update trigger for DSOV table
    cursor.execute("""
                    CREATE TRIGGER FK_DSOV_ID_UPDATE_TRIGGER
                    BEFORE UPDATE ON DSOV_DST
                    FOR EACH ROW BEGIN
                        SELECT RAISE(ROLLBACK, 'UPDATE ON TABLE "DSOV_DST" VIOLATES FOREIGN KEY CONSTRAINT "FK_DSOV_ID"')
                        WHERE  NEW.DSOV_ID IS NOT NULL
                                AND (SELECT ID FROM DSOV WHERE id = NEW.DSOV_ID) IS NULL;
                    END;
                    """) 

    # Create insert trigger for DSOV table
    cursor.execute("""
                    CREATE TRIGGER FK_DST_ID_INSERT_TRIGGER
                    BEFORE INSERT ON DSOV_DST
                    FOR EACH ROW BEGIN
                        SELECT RAISE(ROLLBACK, 'INSERT ON TABLE "DSOV_DST" VIOLATES FOREIGN KEY CONSTRAINT "FK_DST_ID"')
                        WHERE  NEW.DST_ID IS NOT NULL
                                AND (SELECT ID FROM DST WHERE id = NEW.DST_ID) IS NULL;
                    END;
                    """)
    
    # Create update trigger for DSOV table
    cursor.execute("""
                    CREATE TRIGGER FK_DST_ID_UPDATE_TRIGGER
                    BEFORE UPDATE ON DSOV_DST
                    FOR EACH ROW BEGIN
                        SELECT RAISE(ROLLBACK, 'UPDATE ON TABLE "DSOV_DST" VIOLATES FOREIGN KEY CONSTRAINT "FK_DST_ID"')
                        WHERE  NEW.DST_ID IS NOT NULL
                                AND (SELECT ID FROM DST WHERE id = NEW.DST_ID) IS NULL;
                    END;
                    """)       
    
    # Create DST table
    cursor.execute("""
                    CREATE TABLE DST (
                      ID INTEGER NOT NULL PRIMARY KEY,
                      SORT_ORDER TEXT,
                      SUBJECT TEXT,
                      PREDICATE TEXT,
                      OBJECT TEXT,
                      LITERAL TEXT,
                      LITERAL_TYPE TEXT,
                      LITERAL_LANGUAGE TEXT
                    )""")
    

    # Create delete trigger for DSO table
    cursor.execute("""
                    CREATE TRIGGER FK_DST_ID_DELETE_TRIGGER
                    BEFORE DELETE ON DST
                    FOR EACH ROW BEGIN
                        DELETE from DSOV_DST WHERE DST_ID = OLD.ID;
                    END;
                    """) 
            
    
    conn.commit()
    conn.close()

def initialize():
    
    # check if the db has been created
    if not os.path.isfile(sqlite3dao.DATABASE_NAME):
        create_database()        
    
if __name__ == '__main__':
    initialize()
    dSOSqlite3DAO = DSOSqlite3DAO()